import java.io.*;
import java.sql.*;
import java.util.ArrayList;

/**
 * This class contains all of the procedures for connecting and manipulation of the database
 * 
 * @author Andrew Gifford, Marc Vandehey
 */
public class DatabaseUtil {
	
	private static String dbPassword;
	
	/**
	 * Returns true if user exists in the database.
	 * 
	 * @param username
	 * @return
	 */
	public static boolean userInDB(String userName){
		String dbURL = null;
		java.sql.Connection conn = null;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			dbURL = "jdbc:mysql://cs346.cs.uwosh.edu:4381/cs342group1";
			//System.out.println("Attempting Connection");
			conn = DriverManager.getConnection(dbURL,"cs342group1", dbPassword);
			//System.out.println("Connection Successful");
		}
		catch(Exception e){
			System.out.println("Error " + e);
		}
		
		ResultSet rs = null;
		PreparedStatement ps = null;
		String query = "select * from Users where username =?";
		boolean inDB = false;
		
		try{
			ps = conn.prepareStatement(query);
			ps.setString(1, userName);
			rs = ps.executeQuery();
			inDB = rs.first(); //first() returns false if result set is empty (user not in db)
		}
		catch(Exception e){
			System.out.println("Error " + e);
		}
		finally{
			try {
				rs.close();
				ps.close();
				conn.close();
			} catch (SQLException e) {
				System.out.println("Error " + e);
			}
		}
		return inDB;
	}
	
	/**
	 * Returns true if the file exists in the database.
	 * 
	 * @param fileName
	 * @return
	 */
	public static boolean fileInDB(String fileName){
		String dbURL = null;
		java.sql.Connection conn = null;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			dbURL = "jdbc:mysql://cs346.cs.uwosh.edu:4381/cs342group1";
			//System.out.println("Attempting Connection");
			conn = DriverManager.getConnection(dbURL,"cs342group1", dbPassword);
			//System.out.println("Connection Successful");
		}
		catch(Exception e){
			System.out.println("Error " + e);
		}
		
		ResultSet rs = null;
		PreparedStatement ps = null;
		String query = "select * from Owners where filename = ?";
		boolean inDB = false;
		
		try{
			ps = conn.prepareStatement(query);
			ps.setString(1, fileName);
			rs = ps.executeQuery();
			inDB = rs.first(); //first() returns false if result set is empty (fileName not in db)
		}
		catch(Exception e){
			System.out.println("Error " + e);
		}
		finally{
			try {
				rs.close();
				ps.close();
				conn.close();
			} catch (SQLException e) {
				System.out.println("Error " + e);
			}
		}
		return inDB;
	}
	
	/**
	 * Returns true if user is the owner of the file.
	 * 
	 * @param userName
	 * @param fileName
	 * @return
	 */
	public static boolean isFileOwner(String userName, String fileName){
		String dbURL = null;
		java.sql.Connection conn = null;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			dbURL = "jdbc:mysql://cs346.cs.uwosh.edu:4381/cs342group1";
			//System.out.println("Attempting Connection");
			conn = DriverManager.getConnection(dbURL,"cs342group1", dbPassword);
			//System.out.println("Connection Successful");
		}
		catch(Exception e){
			System.out.println("Error " + e);
		}
		
		ResultSet rs = null;
		PreparedStatement ps = null;
		String query = "select username from Owners where filename = ?";
		String owner = "";
		
		try{
			ps = conn.prepareStatement(query);
			ps.setString(1, fileName);
			rs = ps.executeQuery();
			rs.next();
			//System.out.println("Attempting to get username");
			owner = rs.getString(1);
			//System.out.println("Got username");

		}
		catch(Exception e){
			System.out.println("Error " + e);
		}
		finally{
			try {
				rs.close();
				ps.close();
				conn.close();
			} catch (SQLException e) {
				System.out.println("Error " + e);
			}
		}
		return userName.trim().toLowerCase().equals(owner);
	}
	
	/**
	 * Returns true if the user can retrieve the file.
	 * 
	 * @param userName
	 * @param fileName
	 * @return
	 */
	public static boolean canRetrieveFile(String userName, String fileName){
		
		String dbURL = null;
		java.sql.Connection conn = null;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			dbURL = "jdbc:mysql://cs346.cs.uwosh.edu:4381/cs342group1";
			//System.out.println("Attempting Connection");
			conn = DriverManager.getConnection(dbURL,"cs342group1", dbPassword);
			//System.out.println("Connection Successful");
		}
		catch(Exception e){
			System.out.println("Error " + e);
		}
		
		ResultSet rs = null;
		PreparedStatement ps = null;
		String query = "select * from Blobs where username = ? and filename = ?";
		boolean canRetrieve = false;

		try{
			ps = conn.prepareStatement(query);
			ps.setString(1, userName);
			ps.setString(2, fileName);
			rs = ps.executeQuery();
			canRetrieve = rs.first(); // first() returns false if result set is empty (no blob for user/file)
		}
		catch(Exception e){
			System.out.println("Error " + e);
		}
		finally{
			try {
				rs.close();
				ps.close();
				conn.close();
			} catch (SQLException e) {
				System.out.println("Error " + e);
			}
		}
		return canRetrieve;
	}
	
	/**
	 * Add a user with their publicKey into the database.
	 * 
	 * @param userName
	 * @param publicKey
	 */
	public static void addUser(String userName, String publicKeyFileName){

		String dbURL = null;
		java.sql.Connection conn = null;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			dbURL = "jdbc:mysql://cs346.cs.uwosh.edu:4381/cs342group1";
			//System.out.println("Attempting Connection");
			conn = DriverManager.getConnection(dbURL,"cs342group1", dbPassword);
			//System.out.println("Connection Successful");
		}
		catch(Exception e){
			System.out.println("Error " + e);
		}
		
		String insertUser = "insert into Users(username, publickey) values (?,?)";
		FileInputStream fis = null;
		PreparedStatement ps = null;
		try{
			conn.setAutoCommit(false);
			File file = new File(publicKeyFileName);
			fis = new FileInputStream(file);
			ps = conn.prepareStatement(insertUser);
			ps.setString(1, userName);
			ps.setBinaryStream(2, fis, (int)file.length());
			//System.out.println("Attempting Insert");
			ps.executeUpdate();
			conn.commit();
			//System.out.println("Insert Successful");
			
		}
		catch(Exception e){
			System.out.println("Error " + e);
		}
		finally{
			try {
				ps.close();
				conn.close();
			} catch (SQLException e) {
				System.out.println("Error " + e);
			}
		}

	}
	
	/**
	 * Sets the owner of a file.
	 * 
	 * @param userName
	 * @param fileName
	 */
	public static void setFileOwner(String userName, String fileName){
		
		String dbURL = null;
		java.sql.Connection conn = null;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			dbURL = "jdbc:mysql://cs346.cs.uwosh.edu:4381/cs342group1";
			//System.out.println("Attempting Connection");
			conn = DriverManager.getConnection(dbURL,"cs342group1", dbPassword);
			//System.out.println("Connection Successful");
		}
		catch(Exception e){
			System.out.println("Error " + e);
		}
		
		String insertOwner = "insert into Owners(username, filename) values (?,?)";
		
		PreparedStatement ps = null;
		try{
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(insertOwner);
			ps.setString(1, userName);
			ps.setString(2, fileName);
			//System.out.println("Attempting Insert");
			ps.executeUpdate();
			conn.commit();
			//System.out.println("Insert Successful");
			
		}
		catch(Exception e){
			System.out.println("Error " + e);
		}
		finally{
			try {
				ps.close();
				conn.close();
			} catch (SQLException e) {
				System.out.println("Error " + e);
			}
		}
	}
	
	/**
	 * Updates the blob in the database. 
	 * 
	 * @param userName
	 * @param fileName
	 */
	public static void overWriteFile(String userName, String fileName){
		
		String dbURL = null;
		java.sql.Connection conn = null;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			dbURL = "jdbc:mysql://cs346.cs.uwosh.edu:4381/cs342group1";
			//System.out.println("Attempting Connection");
			conn = DriverManager.getConnection(dbURL,"cs342group1", dbPassword);
			//System.out.println("Connection Successful");
		}
		catch(Exception e){
			System.out.println("Error " + e);
		}
		
		String updateBlob = "update Blobs set zip= ? where username = ? and filename = ?"; 
		FileInputStream fis = null;
		PreparedStatement ps = null;
		try{
			conn.setAutoCommit(false);
			File file = new File(fileName+".temp");
			fis = new FileInputStream(file);
			ps = conn.prepareStatement(updateBlob);
			ps.setBinaryStream(1, fis, (int)file.length());
			ps.setString(2, userName);
			ps.setString(3, fileName);
			//System.out.println("Attempting Update");
			ps.executeUpdate();
			conn.commit();
			//System.out.println("Update Successful");
			
		}
		catch(Exception e){
			System.out.println("Error " + e);
		}
		finally{
			try {
				ps.close();
				conn.close();
			} catch (SQLException e) {
				System.out.println("Error " + e);
			}
		}
	}
	
	/**
	 * Retrieve a file for user.  The file blob_fileName will be created.
	 * 
	 * @param username
	 * @param fileName
	 */
	public static void retrieveFile(String userName, String fileName){

		String dbURL = null;
		java.sql.Connection conn = null;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			dbURL = "jdbc:mysql://cs346.cs.uwosh.edu:4381/cs342group1";
			//System.out.println("Attempting Connection");
			conn = DriverManager.getConnection(dbURL,"cs342group1", dbPassword);
			//System.out.println("Connection Successful");
		}
		catch(Exception e){
			System.out.println("Error " + e);
		}
		
		ResultSet rs = null;
		PreparedStatement ps = null;
		String query = "select zip from Blobs where username = ? and filename = ?";
		Blob blob = null;

		try{
			ps = conn.prepareStatement(query);
			ps.setString(1, userName);
			ps.setString(2, fileName);
			rs = ps.executeQuery();
			rs.next();
			//System.out.println("Attempting to get blob");
			blob = rs.getBlob(1);
			//System.out.println("Got blob of length " + blob.length());

		}
		catch(Exception e){
			System.out.println("Error " + e);
		}
		finally{
			try {
				rs.close();
				ps.close();
				conn.close();
			} catch (SQLException e) {
				System.out.println("Error " + e);
			}
		}
		
		try {
			FileOutputStream out = new FileOutputStream("blob_" + fileName);
			BufferedInputStream in = new BufferedInputStream(blob.getBinaryStream());
			int b;
			byte[] buffer = new byte[10240];
			while((b = in.read(buffer, 0, 10240)) != -1){
				out.write(buffer, 0, b);
			}
			out.close();
		} catch (Exception e) {
			System.out.println("Error " + e);
		}
	}
	
	/**
	 * Saves a new blob to the database.
	 * 
	 * @param userName
	 * @param fileName
	 */
	public static void saveFile(String userName, String fileName){
		
		String dbURL = null;
		java.sql.Connection conn = null;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			dbURL = "jdbc:mysql://cs346.cs.uwosh.edu:4381/cs342group1";
			//System.out.println("Attempting Connection");
			conn = DriverManager.getConnection(dbURL,"cs342group1", dbPassword);
			//System.out.println("Connection Successful");
		}
		catch(Exception e){
			System.out.println("Error " + e);
		}
		
		String insertBlob = "insert into Blobs(username, filename, zip) values (?,?,?)";
		FileInputStream fis = null;
		PreparedStatement ps = null;
		try{
			conn.setAutoCommit(false);
			File file = new File(fileName + ".temp");
			fis = new FileInputStream(file);
			ps = conn.prepareStatement(insertBlob);
			ps.setString(1, userName);
			ps.setString(2, fileName);
			ps.setBinaryStream(3, fis, (int)file.length());
			//System.out.println("Attempting Insert");
			ps.executeUpdate();
			conn.commit();
			//System.out.println("Insert Successful");
			
		}
		catch(Exception e){
			System.out.println("Error " + e);
		}
		finally{
			try {
				ps.close();
				conn.close();
			} catch (SQLException e) {
				System.out.println("Error " + e);
			}
			
		}
	}

	/**
	 * Creates user's .pub key file.
	 * 
	 * @param userName
	 */
	public static void getPublicKey(String userName){
		String dbURL = null;
		java.sql.Connection conn = null;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			dbURL = "jdbc:mysql://cs346.cs.uwosh.edu:4381/cs342group1";
			//System.out.println("Attempting Connection");
			conn = DriverManager.getConnection(dbURL,"cs342group1", dbPassword);
			//System.out.println("Connection Successful");
		}
		catch(Exception e){
			System.out.println("Error " + e);
		}
		
		ResultSet rs = null;
		PreparedStatement ps = null;
		String query = "select publickey from Users where username = ?";
		Blob blob = null;
		
		try{
			ps = conn.prepareStatement(query);
			ps.setString(1, userName);
			rs = ps.executeQuery();
			rs.next();
			//System.out.println("Attempting to get blob");
			blob = rs.getBlob(1);
			//System.out.println("Got blob of length " + blob.length());

		}
		catch(Exception e){
			System.out.println("Error " + e);
		}
		finally{
			try {
				rs.close();
				ps.close();
				conn.close();
			} catch (SQLException e) {
				System.out.println("Error " + e);
			}
		}
		
		try {
			FileOutputStream out = new FileOutputStream(userName + ".pub");
			BufferedInputStream in = new BufferedInputStream(blob.getBinaryStream());
			int b;
			byte[] buffer = new byte[10240];
			while((b = in.read(buffer, 0, 10240)) != -1){
				out.write(buffer, 0, b);
			}
			out.close();
		} catch (Exception e) {
			System.out.println("Error " + e);
		}

	}
	
	/**
	 * Checks to see whether or not a connection to the database can be made.
	 * 
	 * @return true if connection was successful
	 */
	public static boolean canConnect(){
		boolean canConnect = false;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			//System.out.println("Connection Successful");
			canConnect = true;
		}
		catch(Exception e){
			System.out.println("Error " + e);
		}
		return canConnect;
	}
	
	/**
	 * Returns a list of all the users in the database.
	 * 
	 * @return list of users
	 */
	public static ArrayList<String> getAllUsers(){
		String dbURL = null;
		java.sql.Connection conn = null;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			dbURL = "jdbc:mysql://cs346.cs.uwosh.edu:4381/cs342group1";
			//System.out.println("Attempting Connection");
			conn = DriverManager.getConnection(dbURL,"cs342group1", dbPassword);
			//System.out.println("Connection Successful");
		}
		catch(Exception e){
			System.out.println("Error " + e);
		}
		
		ResultSet rs = null;
		PreparedStatement ps = null;
		String query = "select username from Users";
		ArrayList<String> users = new ArrayList<String>();
		
		try{
			ps = conn.prepareStatement(query);
			rs = ps.executeQuery();
			while(rs.next()){
				users.add(rs.getString(1));
			}
		}
		catch(Exception e){
			System.out.println("Error " + e);
		}
		finally{
			try {
				rs.close();
				ps.close();
				conn.close();
			} catch (SQLException e) {
				System.out.println("Error " + e);
			}
		}
		return users;
		
	}
	
	/**
	 * Returns a list of files that the user can open.
	 * 
	 * @param userName
	 * @return list of file names
	 */
	public static ArrayList<String> getUsersFileNames(String userName){
		String dbURL = null;
		java.sql.Connection conn = null;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			dbURL = "jdbc:mysql://cs346.cs.uwosh.edu:4381/cs342group1";
			//System.out.println("Attempting Connection");
			conn = DriverManager.getConnection(dbURL,"cs342group1", dbPassword);
			//System.out.println("Connection Successful");
		}
		catch(Exception e){
			System.out.println("Error " + e);
		}
		
		ResultSet rs = null;
		PreparedStatement ps = null;
		String query = "select filename from Blobs where username = ?";
		ArrayList<String> files = new ArrayList<String>();
		
		try{
			ps = conn.prepareStatement(query);
			ps.setString(1, userName);
			rs = ps.executeQuery();
			while(rs.next()){
				files.add(rs.getString(1));
			}
		}
		catch(Exception e){
			System.out.println("Error " + e);
		}
		finally{
			try {
				rs.close();
				ps.close();
				conn.close();
			} catch (SQLException e) {
				System.out.println("Error " + e);
			}
		}
		return files;
		
	}

	
	/**
	 * Set the password used for logging on the the database.
	 * @param password
	 */
	public static void setDBPassword(String password){
		dbPassword = password;
	}
	
	/**
	 * Allows the user to delete a file that they created from the Blob
	 * @param fileName	The filename that is to be deleted
	 */
	public static boolean deleteFile(String fileName)
	{
		String dbURL = null;
		java.sql.Connection conn = null;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			dbURL = "jdbc:mysql://cs346.cs.uwosh.edu:4381/cs342group1";
			//System.out.println("Attempting Connection");
			conn = DriverManager.getConnection(dbURL,"cs342group1", dbPassword);
			//System.out.println("Connection Successful");
		}
		catch(Exception e){
			System.out.println("Error " + e);
			return false;
		}
		
		PreparedStatement ps = null;
		String query = "delete from Blobs where filename = ?";
 
		try{
			ps = conn.prepareStatement(query);
			ps.setString(1, fileName);
			ps.executeUpdate();
		}
		catch(Exception e){
			System.out.println("Error " + e);
			return false;
		}
		finally{
			try {
				ps.close();
				conn.close();
			} catch (SQLException e) {
				System.out.println("Error " + e);
				return false;
			}
		}
		return true;
	}
	/**
	 * Allows the user to delete a file from their owner table to free up
	 * the file name for whomever else needs it
	 * @param fileName	The filename that is to be deleted
	 */
	public static boolean deleteFileFromOwnerTable(String fileName)
	{
		String dbURL = null;
		java.sql.Connection conn = null;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			dbURL = "jdbc:mysql://cs346.cs.uwosh.edu:4381/cs342group1";
			//System.out.println("Attempting Connection");
			conn = DriverManager.getConnection(dbURL,"cs342group1", dbPassword);
			//System.out.println("Connection Successful");
		}
		catch(Exception e){
			System.out.println("Error " + e);
			return false;
		}
		
		PreparedStatement ps = null;
		String query = "delete from Owners where filename = ?";
 
		try{
			ps = conn.prepareStatement(query);
			ps.setString(1, fileName);
			ps.executeUpdate();
		}
		catch(Exception e){
			System.out.println("Error " + e);
			return false;
		}
		finally{
			try {
				ps.close();
				conn.close();
			} catch (SQLException e) {
				System.out.println("Error " + e);
				return false;
			}
		}
		return true;
	}
}
